package cn.learn.office.excel_v2;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 导入Excel表格读取数据
 *
 * @author huangyezhan
 * @date 2018年12月23日22:01
 */
@Deprecated
public class ImportExcelUtil {

    /**
     * 文件直接导入Excel表格
     *
     * @param file       【必填】文件
     * @param titleNames 【选填1】优选选择,Excel的标题名称,可以不填前提:Excel表格的首行(最靠近数据的那行标题)必须是正真的标题行
     * @param clumNum    【选填2】最后选择,如果填了标题名称,这个可以不用填,前提:Excel表格的首行(最靠近数据的那行标题)必须是真正的标题行
     * @param tileNum    【可不填】标题可能不止占一行【必填】,标题只有一行的时候可不填或者填1,如果没有标题填null
     */
    public static List<Map<String, Object>> importExcel(File file, String[] titleNames, Integer clumNum, Integer tileNum) {
        InputStream               input     = null;
        Workbook                  wb        = null;
        int                       columnNum = 0;
        Map<String, Object>       map       = null;
        List<Map<String, Object>> list      = new ArrayList<>();
        try {
            input = new FileInputStream(file);
            if (file.toString().lastIndexOf(".xlsx") > 0) {
                wb = new XSSFWorkbook(input);
            } else {
                wb = new HSSFWorkbook(input);
            }
            if (clumNum != null && clumNum > 0) {
                columnNum = clumNum;
            }
            if (titleNames != null && titleNames.length > 0) {
                columnNum = titleNames.length;
            }
            if (tileNum == null) {
                tileNum = 1;
            }


            //根据函数读取表格的值
            FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
            //读取Excel
            Sheet sheet  = wb.getSheetAt(0);
            int   rowNum = sheet.getLastRowNum();
            Row   row    = null;
            Cell  cell   = null;

            //不给标题，自己去读取标题
            if (titleNames == null) {
                row = sheet.getRow(tileNum - 1);
                if (columnNum < 1) {
                    columnNum = sheet.getRow(0).getPhysicalNumberOfCells();
                }
                titleNames = new String[columnNum];
                for (int i = 0; i < columnNum; i++) {
                    titleNames[i] = row.getCell(i).getRichStringCellValue().toString();
                }
            }


            //读取正真的数据
            for (int i = tileNum; i <= rowNum; i++) {
                row = sheet.getRow(i);
                map = new HashMap<>();
                for (int j = 0; j < columnNum; j++) {
                    cell = row.getCell(j);
                    Object o = getCellValue(cell, formulaEvaluator);
                    map.put(titleNames[j], o);
                }
                list.add(map);
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            ExportExcelUtil.close(input, null, wb);
        }
        return null;
    }

    /**
     * 转换单元格格式
     *
     * @param cell             单元格
     * @param formulaEvaluator 公式对象
     * @return 返回获取出单元格的内容
     */
    private static Object getCellValue(Cell cell, FormulaEvaluator formulaEvaluator) {
        if (cell != null) {
            switch (cell.getCellTypeEnum()) {
                case STRING:
                    return cell.getRichStringCellValue().toString();
                case NUMERIC:
                    return cell.getNumericCellValue();
                case FORMULA:
                    String s = String.valueOf(formulaEvaluator.evaluate(cell));
                    String[] split = s.split("\"");
                    return split[1];
                default:
                    return null;
            }
        }
        return null;
    }


}
